package com.java.bean;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import com.java.db.DBConnect;
import com.java.model.MusicInfo;

public class MusicBean {

	static Connection connection;

	public static ArrayList<MusicInfo> getList(String tag, String s1, String s2)
			throws ClassNotFoundException, SQLException {
		connection = DBConnect.getConnection();
		ArrayList<MusicInfo> list = new ArrayList<MusicInfo>();

		Statement statement = connection.createStatement();
		String sql = "SELECT * FROM music.music ORDER BY " + tag
				+ " DESC LIMIT  " + s1 + "," + s2 + ";";
		System.out.println(sql);
		ResultSet resultSet = statement.executeQuery(sql);
		while (resultSet.next()) {
			MusicInfo info = new MusicInfo(resultSet.getString(1),
					resultSet.getString(2), resultSet.getString(3),
					resultSet.getString(4), resultSet.getString(5),
					resultSet.getString(6), resultSet.getString(7),
					resultSet.getString(8));
			list.add(info);

		}
		System.out.println(list.size());
		return list;
	}

	public static ArrayList<MusicInfo> getList(String tag, String keywords,
			String s1, String s2) throws ClassNotFoundException, SQLException {
		connection = DBConnect.getConnection();
		ArrayList<MusicInfo> list = new ArrayList<MusicInfo>();

		Statement statement = connection.createStatement();
		String sql = "SELECT * FROM music.music where uploader='" + keywords
				+ "' ORDER BY " + tag + " DESC LIMIT  " + s1 + "," + s2 + ";";
		System.out.println(sql);
		ResultSet resultSet = statement.executeQuery(sql);
		while (resultSet.next()) {
			MusicInfo info = new MusicInfo(resultSet.getString(1),
					resultSet.getString(2), resultSet.getString(3),
					resultSet.getString(4), resultSet.getString(5),
					resultSet.getString(6), resultSet.getString(7),
					resultSet.getString(8));
			list.add(info);

		}
		System.out.println(list.size());
		return list;
	}

	public static ResultSet getSong(String id) throws ClassNotFoundException,
			SQLException {
		ResultSet resultSet;
		connection = DBConnect.getConnection();
		Statement statement = connection.createStatement();
		resultSet = statement
				.executeQuery("SELECT * FROM music.music WHERE id='" + id + "'");
		return resultSet;
	}

	public static boolean changePlayTimes(String id, String times)
			throws ClassNotFoundException, SQLException {
		connection = DBConnect.getConnection();
		Statement statement = connection.createStatement();
		return statement.execute("UPDATE `music`.`music` SET `play`='" + times
				+ "' WHERE `id`='" + id + "';");
	}

	public static boolean changeDownTimes(String id, String times)
			throws ClassNotFoundException, SQLException {
		connection = DBConnect.getConnection();
		Statement statement = connection.createStatement();
		return statement.execute("UPDATE `music`.`music` SET `download`='"
				+ times + "' WHERE `id`='" + id + "';");
	}

	public static boolean Insert(String name, String uploader, String player,
			String path, String date) throws ClassNotFoundException,
			SQLException {
		connection = DBConnect.getConnection();
		Statement statement = connection.createStatement();
		return statement
				.execute("INSERT INTO `music`.`music` (`name`, `uploader`, `play`, `download`, `url`, `uptime`, `player`) VALUES ('"
						+ name
						+ "', '"
						+ uploader
						+ "', '0', '0', '"
						+ path
						+ "', '" + date + "', '" + player + "');");
	}

	public static ArrayList<MusicInfo> search(String tag)
			throws ClassNotFoundException, SQLException {
		connection = DBConnect.getConnection();
		ArrayList<MusicInfo> list = new ArrayList<MusicInfo>();

		Statement statement = connection.createStatement();
		String sql = "select * from music.music where name like'%" + tag
				+ "%' or player like '%" + tag + "%' order by play";

		System.out.println(sql);
		ResultSet resultSet = statement.executeQuery(sql);
		while (resultSet.next()) {
			MusicInfo info = new MusicInfo(resultSet.getString(1),
					resultSet.getString(2), resultSet.getString(3),
					resultSet.getString(4), resultSet.getString(5),
					resultSet.getString(6), resultSet.getString(7),
					resultSet.getString(8));
			list.add(info);

		}
		System.out.println(list.size());
		return list;
	}
}
